package com.manager.commons.utils.poi;


import com.manager.commons.log.LogFactory;
import com.manager.commons.utils.DateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * excel生成
 * Created by wcy on 2016/3/21.
 */
public class ExportExcelService {
    protected static Logger logger = LogFactory.getLogger(ExportExcelService.class);

    /**
     * 导出用户
     *
     * @param request
     * @param response
     */
    public void export(HttpServletRequest request, HttpServletResponse response, String title, String[] headers, List<List<String>> listCell) {
        OutputStream out = null;
        try {
            // 文件名
            String fileName = "用户列表_" + (DateUtil.format(new Date(), "yyyyMMddHHmmss")) + ".xls";
            // 获取浏览器类型,处理文件名乱码问题
            String userAgent = request.getHeader("user-agent").toLowerCase();
            if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.UTF_8);
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            out = response.getOutputStream();
            exportExcel(title, headers, listCell, out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                if (null != out) {
                    out.close();
                }
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    public static void exportExcel(String title, String[] headers, List<List<String>> listCell, OutputStream out) throws Exception {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(title);
            // 将第一列(ID)隐藏
            // sheet.setColumnHidden(0, true);
            // 设置表格默认列宽15个字节
            sheet.setDefaultColumnWidth(15);

            // 生成一种字体
            HSSFFont font = workbook.createFont();
            // 设置字体颜色
            font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置字体大小
            font.setFontHeightInPoints((short) 10);
            font.setFontName("宋体");

            // 单元格锁定的样式，不可以编辑
            HSSFCellStyle lockStyle = workbook.createCellStyle();
            lockStyle.setLocked(true);
            // 垂直居中
            lockStyle.setAlignment(HorizontalAlignment.CENTER);
            // 水平居中
            lockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            lockStyle.setFont(font);

            // 单元格不锁定的样式，可编辑
            HSSFCellStyle unlockStyle = workbook.createCellStyle();
            unlockStyle.setLocked(false);
            // 垂直居中
            unlockStyle.setAlignment(HorizontalAlignment.CENTER);
            // 水平居中
            unlockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            unlockStyle.setFont(font);

            // 生成通用样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 垂直居中
            style.setAlignment(HorizontalAlignment.CENTER);
            // 水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            // 把字体应用到当前样式
            style.setFont(font);
            // 生成表格标题
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) 500);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(new HSSFRichTextString(headers[i]));
            }
            for (int i = 0; i < listCell.size(); i++) {
                row = sheet.createRow((i + 1));
                List<String> values = listCell.get(i);
                int cell_index = 0;
                for (String v : values) {
                    HSSFCell cell = row.createCell(cell_index++);
                    cell.setCellStyle(lockStyle);
                    cell.setCellValue(new HSSFRichTextString(v == null ? "" : v));
                }
            }
            workbook.write(out);
            workbook.close();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }

}
